package br.ufrj.neg.artigo.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;

import org.apache.commons.lang.time.DateFormatUtils;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

import br.ufrj.neg.artigo.bean.ArtigoBean;
import br.ufrj.neg.bean.TraducaoBean;
import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.model.TraducaoModel;
import br.ufrj.neg.util.Data;

public class ArtigoModel {
	
	public int cadastraArtigo(ArtigoBean artigo){
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		Connection conn = ServiceLocator.getConnection();
		
		try{
			conn.setAutoCommit(false);
			
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
			.getLargeObjectAPI();
			
			// Create a new large object
			long oid = lobj.createLO(LargeObjectManager.READ
					| LargeObjectManager.WRITE);
			
			// Open the large object for writing
			LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
			
			obj.write(artigo.getArtigoAsByte(), 0,artigo.getArtigoAsByte().length);
			
			obj.close();
			
			Date dt = new Date();
			long nowLong = dt.getTime();
			String hoje = DateFormatUtils.format(nowLong, "dd/MM/yyyy");
			String sql = "INSERT INTO artigo( arti_dt_data_upload, arti_bl_arquivo, "
				+ "arti_cd_ativo) values(to_date(?,'dd/mm/yyyy'),?,?) RETURNING arti_cd_id";
			ps = conn.prepareStatement(sql);
			ps.setString(1, artigo.getDataCadastro().getDataBr());
			ps.setLong(2, oid);
			ps.setInt(3, artigo.getAtivoB());

			rs = ps.executeQuery();
			rs.next();
			int id = -1;
			id = rs.getInt(1);
			artigo.setId(id);
					
			conn.commit();
			
			TraducaoModel.insere(artigo);

			return id;
			
		} catch (Exception e) {
			System.out.println(e);
			return -1;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
		
	}

	/**
	 * @param aB ArtigoBean que deve ter seu id ja setado para usar este metodo.
	 * @return O mesmo bean, preenchido adequadamente do banco.
	 */
	public ArtigoBean getArtigo(ArtigoBean aB){
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		
		try{
			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn).getLargeObjectAPI();
						
			String sql = "select * from artigo where arti_cd_id = ?";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, aB.getId());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				// Open the large object for reading
				long oid = rs.getLong("arti_bl_arquivo");
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
			
				
				aB.setAtivo(rs.getInt("arti_cd_ativo"));
				aB.setDataCadastro(new Data(rs.getDate("arti_dt_data_upload")));
				aB.setId(aB.getId());				
				//aB.setNome(rs.getString("arti_ds_nome"));
				
				TraducaoBean traducao = TraducaoModel.getTraducoesElemento(aB);			
				aB.setDicionario( traducao.getDicionario() );						
			
				// Read the data
				byte buf[] = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				aB.setArtigoAsByte(buf);
				obj.close();
			}
			rs.close();
			ps.close();
			conn.close();

			return aB;
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return null;

	}

	public ArrayList<ArtigoBean> getArtigos(ArtigoBean aB){
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		
		ArrayList<ArtigoBean> artigos = new ArrayList<ArtigoBean>();
		
		try{
			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn).getLargeObjectAPI();
			
			String sql = "SELECT * FROM artigo as ar ORDER BY ar.arti_cd_id";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			ArtigoBean aB_elem;
			while (rs.next()) {
				aB_elem = aB.getNewInstance();
				
				// Open the large object for reading
				long oid = rs.getLong("arti_bl_arquivo");
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
				
				// Read the data
				byte buf[] = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				aB_elem.setArtigoAsByte(buf);
				obj.close();											
				
				aB_elem.setId(rs.getInt("arti_cd_id"));
				
				//aB_elem.setNome(rs.getString("arti_ds_nome"));
				aB_elem.setAtivo(rs.getInt("arti_cd_ativo"));
				aB_elem.setDataCadastro(new Data(rs.getDate("arti_dt_data_upload")));
				
				TraducaoBean traducao = TraducaoModel.getTraducoesElemento(aB_elem);			
				aB_elem.setDicionario( traducao.getDicionario() );					
				
				/*
				TraducaoBean traducao = TraducaoModel.getTraducoesElemento(aB_elem);			
				aB_elem.setDicionario( traducao.getDicionario() );
				String nome = aB_elem.getDicionario().get(aB_elem.CAMPO_NOME).get(aB_elem.getIdioma());				
				aB_elem.setNome(nome);
				 */
				
				artigos.add(aB_elem);
			}
			
			rs.close();
			ps.close();
			conn.close();

			return artigos;
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return null;
				
	}

	public ArrayList<ArtigoBean> getArtigosAtivos(ArtigoBean aB){
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		
		ArrayList<ArtigoBean> artigos = new ArrayList<ArtigoBean>();
		
		try{			
			// Get the Large Object Manager to perform operations with
			LargeObjectManager lobj = ((org.postgresql.PGConnection) conn).getLargeObjectAPI();
			
			String sql = "SELECT * FROM artigo as ar WHERE ar.arti_cd_ativo = 1 ORDER BY ar.arti_cd_id ";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			ArtigoBean aB_elem;
			while (rs.next()) {
				aB_elem  = aB.getNewInstance();
				
				// Open the large object for reading
				long oid = rs.getLong("arti_bl_arquivo");
				LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
				
				// Read the data
				byte buf[] = new byte[obj.size()];
				obj.read(buf, 0, obj.size());
				aB_elem.setArtigoAsByte(buf);
				obj.close();
				
				aB_elem.setId(rs.getInt("arti_cd_id"));
				//aB_elem.setNome(rs.getString("arti_ds_nome"));
				aB_elem.setAtivo(rs.getInt("arti_cd_ativo"));
				aB_elem.setDataCadastro(new Data(rs.getDate("arti_dt_data_upload")));
				
				TraducaoBean traducao = TraducaoModel.getTraducoesElemento(aB_elem);			
				aB_elem.setDicionario( traducao.getDicionario() );							
				
				artigos.add(aB_elem);
			}
			
			rs.close();
			ps.close();
			conn.close();

			return artigos;
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return null;
				
	}
	
	public boolean excluirArtigo(ArtigoBean aB){
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		
		try{							
			TraducaoModel.remove(aB);			
			
			String sql = "DELETE from artigo WHERE arti_cd_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, aB.getId());
			ps.executeUpdate();
			conn.commit();			
			
			return true;
		}catch (Exception e) {
			System.out.println(e.getMessage());
		}
		return false;
	}
	
	public boolean editarArtigo(ArtigoBean aB){
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		
		try{							
			TraducaoModel.atualiza(aB);				
			
			conn.setAutoCommit(false);
			
			String sql = "UPDATE artigo SET arti_cd_ativo= ? WHERE arti_cd_id = ?";
			ps = conn.prepareStatement(sql);
			//ps.setString(1, aB.getNome());
			ps.setInt(1, aB.getAtivoB());
			ps.setInt(2, aB.getId());
			ps.executeUpdate();
			conn.commit();
			
			return true;
			
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
		
	}
	
	
	
}
